package java6699.lesson11;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.Statement;

/**
 * @program: java_zzx
 * @description:
 * @author: zzx
 * @create: 2022-11-12 16:05
 **/
public class SimpleDML6699 {
    @Test
    public void create6699() throws Exception {
        String sql = "CREATE TABLE `xslist`  (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  `sno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL " +
                "COMMENT '学号',\n" +
                "  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL " +
                "COMMENT '姓名',\n" +
                "  `lx1` int(11) NULL DEFAULT NULL COMMENT '练习1的成绩',\n" +
                "  `lx2` int(11) NULL DEFAULT NULL COMMENT '练习2的成绩',\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE =" +
                " utf8_general_ci ROW_FORMAT = Dynamic;\n";
        System.out.println(execute6699(sql));
    }
    @Test
    public void insert6699() throws Exception {
        String sql = "INSERT INTO xsList(sno,sname) value ('212106699','周梓希')";
        System.out.println(execute6699(sql));
    }
    @Test
    public void update6699() throws Exception {
        String sql = "UPDATE xsList SET lx1=id*2 , lx2=lx1+id";
        System.out.println(execute6699(sql));
    }
    @Test
    public void delete6699() throws Exception {
        String sql = "DELETE FROM xsList WHERE lx1%3=0";
        System.out.println(execute6699(sql));
    }
    public int execute6699(String sql) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");//加载注册驱动
        //连接数据库
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db3",
                "root","1234");
        Statement st = conn.createStatement();
        int i = st.executeUpdate(sql);
        System.out.println("添加：" + i);
        st.close();
        conn.close();
        return i;
    }
}
